create  table jms_dm.dm_terminal_sign_union_retention_network_sum_dt
(
code                       varchar(100) COMMENT '网点code' ,
city_id                    varchar(100) COMMENT '城市id' ,
provider_id                varchar(100) COMMENT '省份id' ,
agent_code                 varchar(100) COMMENT '代理区code' ,
name                       varchar(100) COMMENT '网点名字' ,
virt_code                  varchar(100) COMMENT '虚拟代理区code' ,
virt_name                  varchar(100) COMMENT '虚拟代理区' ,
regional_id                varchar(100) COMMENT '管理大区id' ,
regional_desc              varchar(100) COMMENT '管理大区' ,
agent_name                 varchar(100) COMMENT '代理区' ,
fran_code                  varchar(100) COMMENT '加盟商code' ,
fran_name                  varchar(100) COMMENT '加盟商' ,
provider_desc              varchar(100) COMMENT '省份' ,
city_desc                  varchar(100) COMMENT '城市' ,
zone_id                    varchar(100) COMMENT '片区id' ,
zone_code                  varchar(100) COMMENT '片区' ,
zone_name                  varchar(100) COMMENT '片区' ,
area_id                    varchar(100) COMMENT '区县id' ,
area_desc                  varchar(100) COMMENT '区县' ,
zone_staff_code            varchar(100) COMMENT '片区负责人编码' ,
zone_staff_name            varchar(100) COMMENT '片区负责人' ,
day_all_sign_22            int(9) COMMENT '22点签收量' ,
day_all_sign_24            int(9) COMMENT '24点签收量' ,
day_need_sign_count        int(9) COMMENT '应签收量' ,
day_sign_rate              decimal(16,2) COMMENT '22点签收率' ,
yesterday_need_sign_count  int(9) COMMENT '昨天应签收量' ,
yesterday_all_sign_22      int(9) COMMENT '昨天22点签收量' ,
yesterday_all_sign_24      int(9) COMMENT '昨天24点签收量' ,
yesterday_sign_rate        decimal(16,2) COMMENT '昨天22点签收率' ,
need_sign_qoq              int(9) COMMENT '应签收环比' ,
retention_count_1d         int(9) COMMENT '一天内滞留' ,
retention_count_3d         int(9) COMMENT '三天内滞留量' ,
retention_count_5d         int(9) COMMENT '五天内滞留' ,
retention_count_7d         int(9) COMMENT '七天内滞留' ,
retention_count_20d        int(9) COMMENT '20天内滞留' ,
retention_over_20d         int(9) COMMENT '超20滞留' ,
retention_sum              int(9) COMMENT '滞留总量（20+超20）' ,
is_contain_difficult       int(9) COMMENT '是否包含问题件' ,
last_fine decimal(16,4) COMMENT '最终罚款' ,
day_sign_rate_1030 decimal(16,2) COMMENT '签收率1030' ,
day_sign_rate_1130 decimal(16,2) COMMENT '签收率1130' ,
day_sign_rate_1400 decimal(16,2) COMMENT '签收率1400' ,
day_sign_rate_1800 decimal(16,2) COMMENT '签收率1800' ,
day_sign_rate_2200 decimal(16,2) COMMENT '签收率2200' ,
date_time  date COMMENT '业务日期'  )
ENGINE=OLAP
DUPLICATE KEY(code,city_id,provider_id,agent_code)
comment '末端签收和滞留网点汇总'
PARTITION BY RANGE (date_time) (
   START ("2023-05-15") END ("2023-07-14") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(`code`) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);

alter table jms_dm.dm_terminal_sign_union_retention_network_sum_dt add column (
       fine_1030 decimal(16,4)  comment'十点半罚款',
       fine_1130 decimal(16,4)  comment'十一点半罚款',
       fine_1400 decimal(16,4)  comment'十四点罚款',
       fine_1800 decimal(16,4)  comment'十八点罚款',
       fine_2200 decimal(16,4)  comment'22点罚款',
       fine_sum decimal(16,4)  comment'总罚款',
       reback_fine decimal(16,4)  comment'返款'
    );